Day four
Use MySQL group by to organise database part 2
使用MySQL- group by 來整理資料庫~2

Hello everyone, it’s Ray!
哈囉大家好，我是Ray！

Today I’m going to share with you the further usage about how to manipulate database with group by, and inserting refined data into a new table.
今天想要跟大家分享group by 的更進一步的操作，如何使用group by 配合select 相對應的選項，新建一個表格，並在新表格內將資料重新整理爲我們需要的row and column。

First, let’s start from the final image got on yesterday. What if we need the total rainfall of months, or years?
首先，延續昨天的進度，如下圖所示，我們將降雨量根據天來做分類，那如果說今天我們需要月的降雨量，或者年雨量總和呢？


請參考以下的code:
Please take a look on code below:

<?php
// SELECT後面的year(date)以及month(date)表示SELECT這兩項資料，括號後的year以及month表示顯示出來的欄位名稱，sum表示加總括號內欄位資料的總和, 括號內的rainfall爲欄位名稱，括號後的表示顯示出來的欄位名稱，一樣使用group by，使資料以月份以及年分來做顯示，order by 表示依照先後順序由先到後作排列。
// The year(date) and month(date) after select mean what data we want, and the second year and month after braces means the name of the column on shown data. Sum means to total all the of values of the column within braces followed by, and the second rainfall means the name of the column on shown data. Use "group by" to make shown data grouped by month and year, and "order by" makes the data arranged in ascending order.


$selectQuery = 'SELECT year(date) year, month(date) month, sum(rainfall) rainfall from rainfall_by_date group by month(date), year(date) order by year(date) asc, month(date) asc;';

// 向資料庫作select 請求
// Make a select request to the database
$selectResult = mysqli_query($dbc, $selectQuery);

// 使用迴圈來重複請求，直到拿出所有位於$selectResult物件中的所有array
// Use while loop to repeat select request until all of the arrays in $selectResult object are taken
while ($selectRow = mysqli_fetch_array($selectResult))
{
    // Insert the data taken from table rainfall_by_date into the table rainfall_by_month
    // 將我們從rainfall_by_date取得的資料insert進新表格rainfall_by_month
    $insertQuery = 'INSERT INTO rainfall_by_month (year, month, rainfall) VALUES("' . $selectRow['year'] . '", "' . $selectRow['month'] . '", "' . $selectRow['rainfall'] . '")';

    // Make an insert request.
    // 作insert請求
    $insertResult = mysqli_query($dbc, $insertQuery);
}

After executing the script above, you will be able to get the new table with refined data, as table below:
執行以上的script之後，可以得到新的表格，如下：


It’s my sharing today, see you guys tomorrow!
以上爲今天分享，我們明天見！